﻿CREATE TRIGGER [dbo].[tr_Loan_Insert]
ON [dbo].[t_Loan] 
FOR INSERT
AS

IF EXISTS(SELECT * FROM inserted WHERE StatusID = 0)BEGIN
	RAISERROR('You are not authorized to create a new account.
Don''t do this anymore!.',16,1)
	RETURN
END

DECLARE @LoanID int SELECT @LoanID = ID FROM inserted
DECLARE @CI int
EXEC @CI = sp__Context_SecurityOff 1

EXEC s_Borrower_Add @LoanID,0

INSERT INTO t_LoanInfo (LoanID) SELECT ID FROM inserted
INSERT INTO t_CarInfo  (LoanID) SELECT ID FROM inserted
INSERT INTO t_Collection(LoanID) SELECT ID FROM inserted
INSERT INTO t_Repossession(LoanID) SELECT ID FROM inserted
INSERT INTO t_Bankruptcy(LoanID) SELECT ID FROM inserted
INSERT INTO t_BuyBack(LoanID) SELECT ID FROM inserted

IF NOT EXISTS(SELECT * FROM t_VerificationSmart VS INNER JOIN inserted i ON i.ID = VS.LoanID)
	INSERT INTO t_VerificationSmart(LoanID) SELECT ID FROM inserted

INSERT INTO t_TrackerRequest(LoanID,StatusID)SELECT ID,106 FROM inserted

EXEC @CI = sp__Context_ArchiveOff 1
INSERT INTO t_Loan_Verification(LoanID, VerificationID)
SELECT i.ID, t_Verification.ID
FROM  inserted i CROSS JOIN t_Verification
EXEC sp__Context @CI


